﻿CREATE PROCEDURE [dbo].[proc_Order_Change_Finish]
(
	@CId int,
	@OId uniqueidentifier,
	@UserId varchar(30),	--录入人(变更审批人)的账号
	@EName nvarchar(30),	--录入人(变更审批人)的姓名
	@CompanyId2 int,		--录入人(变更审批人)的公司编号
	@xjORyh varchar(10)
)
AS
Begin
Begin Tran
--定义错误计数器
Declare @errCount Int
--新增成单额、实有现金、实有银行、变更后的成单总额(申请人录入)
Declare @je decimal(18,2),@xj decimal(18,2),@yh decimal(18,2),@newje_lr decimal(18,2)
--发生业务的公司编号和部门编号、录入人的公司编号、成单编号、成单类型
Declare @CompanyId int,@DeptId int,@CompanyId_LRR int,@OrderNumber varchar(20),@NOE_Flag int
--原成单金额、新实收、新成单金额、新增应收、原实收,变更类型
Declare @oldje decimal(18,2),@shishou decimal(18,2),@newje decimal(18,2),@ysh decimal(18,2),@shishou_out decimal(18,2),@ChangeType int
--成单状态、销售账号、销售姓名、客户编号、客户名称,成单日期(以审核日期为准)
Declare @StateId int,@SalesmanAccount varchar(20),@Salesman nvarchar(50),@CusId varchar(50),@CusName nvarchar(100),@AuditDate datetime
--实有现金/银行标志和名称、最后一次项目分析的日期
Declare @PaymentTypeId int,@PaymentTypeName nvarchar(20),@Today_RQ datetime

--初始化
Set @CusId=Null
Set @CusName=Null
Set @oldje=Null
Set @CompanyId=Null
Set @xj=0
Set @yh=0
Set @ysh=0
Set @NOE_Flag=Null
Set @Today_RQ=Null
Set @ChangeType=Null
Set @errCount=0

--取客户编号、客户名称、成单状态、实收金额、销售人员账号、姓名、公司编号、部门编号、成单类型、成单日期
Select @CusId=CusId,@CusName=CusName,@StateId=StateId,@shishou=IsNull(ShiShouPrice,0),@DeptId=DepId,@AuditDate=AuditDate,
@OrderNumber=IsNull(OrderId,''),@CompanyId=CompanyId,@NOE_Flag=NOE_Flag From [Order] Where OId=@OId

--取变更前成单总额、变更后的成单总额(申请人录入)、本次变更类型、变更申请人账号、姓名
Select @oldje=oldje,@newje_lr=IsNull(newje,0),@ChangeType=ChangeType,@SalesmanAccount=shqr,@Salesman=shqrxm
From Order_Change Where Id=@CId And OId=@OId And flag=1

--变更记录不存在或成单不存在
If @CusId Is Null Or @CusName Is Null Or @CompanyId Is Null Or @oldje Is Null
	Or @ChangeType Is Null Or @NOE_Flag Is Null Or @AuditDate Is Null
Begin
	Set @errCount=@errCount+1
	Goto errHandler
	Return
End

--保存原始实收金额，稍后(可能)存到支出备注里--退款时
Set @shishou_out=@shishou

--取变更后的成单总额(以实际成单数据为准，不以申请人填写的为准)
Select @newje=IsNull((Select Sum(UnitPrice*Quantity) From Order_Item Where OId=@OId),0)+IsNull((Select Sum(UnitPrice*Quantity) From Order_Change_Item Where CId=@CId And OId=@OId),0)

If @newje<>@newje_lr	--变更后的成单总额(申请人录入)与项目分析的结果不符
Begin
	Set @errCount=@errCount+1
	Goto errHandler
	Return
End

--By 赵洪涛 2012-08-13 将16:30后的数据调整到第2天 Begin--
Declare @nDays int,@nHour int,@nMinute int,@dToday DateTime

Set @dToday=dbo.Get_SetDateTime()
--By 赵洪涛 2012-08-13 将16:30后的数据调整到第2天 End --

Set @je=@newje-@oldje	--成单总额的变化(=新增应收)

If @je<0	--成单总额减少
Begin
	If @newje<@shishou	--新的成单总额小于实收：有退款了！记录现金和银行的变化(暂时，因为退款已经录支出了)
	Begin
		Set @ysh=@shishou-@oldje	--新增应收=实收-原成单额(应收<0)

		--插入一条负认款记录(退款金额=新成单金额-原实收)
		Insert Into Order_PaymentRecord(RId,OId,CusId,CusName,SalesmanAccount,Salesman
		,CreateDate,Prices,PaymentTypeId,PaymentType,BankId,Bank,SWId,SWName,shjbh,FinanceFlag,FinanceDate,CompanyId,Description)
		Values(@CId,@OId,@CusId,@CusName,@SalesmanAccount,@Salesman
		,@dToday,@newje-@shishou,1,'现金',1,'现金',2,'尾款','退款',1,'1900-01-01',@CompanyId,'成单变更产生的退款')

		If @@ROWCOUNT<1 Or @@error <> 0
		Begin
			Set @errCount=@errCount+1
			Goto errHandler
			Return
		End

		If @xjORyh='1'	--实有现金变化
			Begin
				Set @PaymentTypeId=1
				Set @PaymentTypeName='现金'
			End
		Else	--实有银行变化
			Begin
				Set @PaymentTypeId=2
				Set @PaymentTypeName='电汇'
			End
		If @@ROWCOUNT<1 Or @@error <> 0
		Begin
			Set @errCount=@errCount+1
			Goto errHandler
			Return
		End

		Set @shishou=@newje			--(此时的)实收=新成单总额
	End
	Else	--新的成单总额仍大于等于实收：不产生退款(即：实收不变)
		Set @ysh=@je	--新增应收=新单总额-原单总额(应收<0)
End
Else	--成单额增加(或不变)，重新计算应收(2012-09-22增加，原变更不产生>0的应收)
	Set @ysh=@je	--新增应收=新单总额-原单总额(应收>=0)

--将临时成单数据追加到正式表
Insert Into Order_Item(OId,ProductId,ProductName,UnitPrice,Quantity
,Salesman,SalesmanAccount,DepId,CompanyId,CreateDate,Occupancy,IsValid,Description,TradeDate)
SELECT OId,ProductId,ProductName,UnitPrice,Quantity
,Salesman,SalesmanAccount,DepId,CompanyId,CreateDate,Null,1,Description,TradeDate
FROM Order_Change_Item Where CId=@CId And OId=@OId

--取最后一次项目分析的日期
Select Top 1 @Today_RQ=CreateDate From Project Where OrderId=@OId Order By CreateDate Desc,Id Desc

--将临时项目分析数据追加到正式表
Insert Into Project(OrderId,CusId,CusName,ProductId,ProductName,SupplierId,SupplierName,CostPrice
,SalePrice,Description,TypeId,TypeName,IsFinish,CompanyId,DeptId,Salesman,SalesmanAccount,CreateUser,CreateDate,FinishDate
,ExecDepartmentId,ExecPerson,ExecPersonName,FinanceAudit,FinanceAuditDate,SCateOneId,SCateTwoId,SubjectId,UpTime,IsHedge)
SELECT OId ,@CusId,@CusName,ProductId,ProductName,SupplierId,SupplierName,CostPrice
,SalePrice,Description,TypeId,TypeName,IsFinish,CompanyId,@DeptId,Salesman,SalesmanAccount,CreateUser,CreateDate,FinishDate
,ExecDepartmentId,ExecPerson,ExecPersonName,FinanceAudit,FinanceAuditDate,SCateOneId,SCateTwoId,SubjectId,UpTime,Case When Project_Id>0 Then -1 Else 0 End
FROM Order_Change_Project Where CId=@CId And OId=@OId

--被对冲的项目分析记录的IsHedge置成-2(对冲记录在上面的语句中已至成-1),用以区分正常数据 By ZHT 2015-01-15
Update Project Set IsHedge=-2 Where OrderId=@OId And id In(Select Project_Id From Order_Change_Project Where CId=@CId And OId=@OId AND Project_Id>0)

--分开置完成标志和完成日期，目的是确保完成日期唯一
Update Project Set FinishDate=@dToday Where OrderId=@OId And IsFinish=0 And FinishDate Is Null
	And id In(Select Project_Id From Order_Change_Project Where CId=@CId And OId=@OId AND Project_Id>0)
--将已被对冲但尚未完成的项目置成完成状态
Update Project Set IsFinish=1 Where OrderId=@OId And IsFinish=0
	And id In(Select Project_Id From Order_Change_Project Where CId=@CId And OId=@OId AND Project_Id>0)

--修改变更产生的数据的创建日期、成单类型
Update Project Set CreateDate=@dToday,NOE_Flag=@NOE_Flag Where OrderId=@OId And CreateDate Is Null

--保存成单变化，为财务日报提供数据
If @Today_RQ Is Not Null	--做过项目分析
Begin
	--If Convert(varchar(10),@Today_RQ,23)<>Convert(varchar(10),@dToday,23)	--不是项目分析当日做的变更
	If Convert(varchar(10),@AuditDate,23)<>Convert(varchar(10),@dToday,23)	--不是成单当日做的变更
	Begin
		--保存原成单总额、新成单总额、对冲的现金还是银行、应收的变化、成单总额的变化
		Insert Into Order_Change_Hedge(CId,CusId,CusName,UpTime,OId,oldje,newje,dch_xj,dch_yy,dch_ysh,dch_chd,CompanyId)
		Values(@CId,@CusId,@CusName,@dToday,@OId,@oldje,@newje,@xj,@yh,@ysh,@je,@CompanyId)

		If @@ROWCOUNT<1 Or @@error <> 0
		Begin
			Set @errCount=@errCount+1
			Goto errHandler
			Return
		End
	End
End

--当日成单又当日变更的，由于不产生对冲数据，所以直接更改成单 --150416加：更改Order表新增应收
If Convert(varchar(10),@AuditDate,23)=Convert(varchar(10),@dToday,23)
	Update [Order] Set F_YingShou=@newje,H_YingShou=@newje-@shishou Where OId=@OId

--置新成单总额和实收为变更后的
If (@StateId=2)	--财务认款了，但尚未进行项目分析或项目分析未完成的，还要更改成单状态(改成已分析)
	Update [Order] Set YingShouPrice=@newje,ShiShouPrice=@shishou,StateId=3 Where OId=@OId
Else	--否则不改变成单状态
	Update [Order] Set YingShouPrice=@newje,ShiShouPrice=@shishou Where OId=@OId

If @@ROWCOUNT<1 Or @@error <> 0
Begin
	Set @errCount=@errCount+1
	Goto errHandler
	Return
End
-----------------------------------------------------2016.9.10 by lht 网站维护费自动完结-----------------------------------------------------------------------------------------

---------------------分析的项目中有“网站维护费”的，自动下发给接口人并该项自动完结----------------
	Declare @Ct int
	SELECT   @Ct=COUNT(*) FROM   Project  WHERE   OrderId = @OId and ProductId=69 and ProductName='网站维护费' and IsHedge=0 and  FinanceAudit=1
	
	IF @Ct > 0  --存在网站维护费项目
	  
	   Declare  @ProjectId uniqueidentifier,
			    @CusName_1 nvarchar(200),
	            @ProductId int,
				@ProductName nvarchar(50),
				@ExecDepartmentId int,
				@ExecPerson nvarchar(50),
				@CompanyId_1 int
     Begin
		   Declare My_Cursor CURSOR --定义游标
		   FOR  select ProjectId,CusName,ProductId,ProductName,ExecDepartmentId,ExecPerson,CompanyId from Project where OrderId=@OId  and ProductId=69 and ProductName='网站维护费' and IsHedge=0 and FinanceAudit=1
		   OPEN My_Cursor; --打开游标
		   FETCH NEXT FROM My_Cursor INTO @ProjectId,@CusName_1,@ProductId,@ProductName,@ExecDepartmentId,@ExecPerson,@CompanyId_1; --读取第一行数据

		   WHILE (@@FETCH_STATUS = 0)
			BEGIN
					 Declare @ExecDepartmentName nvarchar(50),@DownPersonName nvarchar(50)
			
					 Select @ExecDepartmentName=DName From Department where Id=@ExecDepartmentId --任务执行人部门名称
					 Select @DownPersonName=EName From Employee Where UserName=@ExecPerson --任务下发人姓名

					 Insert Into Project_Task --增加一条任务，状态为完成
						(
							FId
							,OrderId
							,ProjectId
							,ProjectName
							,ProductId
							,CusName
							,Title
							,WebType
							,Description
							,DownPerson
							,DownPersonName
							,ExecDepartmentId
							,ExecDepartmentName
							,ExecPerson
							,ExecPersonName
							,StartDate
							,EndDate
							,IsFinish
							,CreateDate
							,CompanyId
							,TypeId
						)
						Values
						(
							@ProjectId
							,@OId
							,@ProjectId
							,@ProductName
							,@ProductId
							,@CusName_1
							,'网站维护费'
							,0
							,'网站维护费--自动完结'
							,@ExecPerson
							,@DownPersonName
							,@ExecDepartmentId
							,@ExecDepartmentName
							,@ExecPerson
							,@DownPersonName
							,GETDATE()
							,GETDATE()
							,1 --完成
							,GETDATE()
							,@CompanyId_1
							,2  --个人
						)

			
					--改变项目完成状态(完成日期不会因为产生新任务而发生变化)
					Update Project Set IsFinish=1 Where ProjectId=@ProjectId

				FETCH NEXT FROM My_Cursor INTO @ProjectId,@CusName_1,@ProductId,@ProductName,@ExecDepartmentId,@ExecPerson,@CompanyId_1; --读取第一行数据
			END
			CLOSE My_Cursor; --关闭游标
			DEALLOCATE My_Cursor; --释放游标
	   
	   		 --改变订单状态
			Update [Order] Set StateId=4 Where OId=@OId And StateId<4
			----------------------------------------------------------------------------------------------------------------------------
      End
------------------------------------------------------------------------------------------------------------------------------------------------



If @ChangeType=1	--退单
Begin
	--分开置完成标志和完成日期，目的是确保完成日期唯一
	Update [Order] Set StateId=6 Where OId=@OId
	Update [Order] Set FinishDate=@dToday Where OId=@OId And FinishDate Is Null
End

--完成变更
--Update Order_Change Set newje=@newje,flag=2 Where Id=@CId And OId=@OId
Update Order_Change Set flag=2 Where Id=@CId And OId=@OId
If @@ROWCOUNT<1 Or @@error <> 0
Begin
	Set @errCount=@errCount+1
	Goto errHandler
	Return
End

errHandler:
	If @errCount>0 Or @@error <> 0
		RollBack Tran	--发生错误
	Else
		Commit Tran    --执行成功

Return 0
End